package daoMySQL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import daoDataFactory.DbObject;
import daoInterface.DaoICategorie;
import domijn.Categorie;
import domijn.Menu;



public class MySQLCategorie extends DbObject implements DaoICategorie {

	
	private Statement stmt = null;
	private ResultSet rs = null;
	@SuppressWarnings("unused")
	private Menu m;
	@SuppressWarnings("unused")
	private Categorie c;
	
	private static MySQLCategorie instance;
	
	public static DaoICategorie instance(){
		if (instance == null) {
			 instance = new MySQLCategorie();
		}
		return instance;
	}

	public void save(Categorie c) {
		 String sqlSave = "INSERT INTO `categorie`(CategorieNaam) "
					+ "	VALUES("
					+ "		'"+ c.getCatName()+"'"
				    + ")";
		try{
			 stmt = openConnection().createStatement();
			 stmt.executeUpdate(sqlSave);
			 
			 System.out.println("MySQLCategorie, public void save, INSERT INTO");			 		
			}
		catch(SQLException sql1){
			 if (sql1.getErrorCode()==1062) {
				 JOptionPane.showMessageDialog(null, "<html><b> "
												  + "Deze categorie bestaat al. <br>"
												  + "Voer a.u.b. een andere categorie naam in. "
												  + "</b></html>");
			 									  }
			else{
				 System.out.println("New category is saved");
			}}}

	
	public void update(Categorie c) {
		System.out.println("Name = " + c.getCatName());
		System.out.println("ID = " + c.getCategorieID());
		String sqlUpdate = "UPDATE"
				+ "		`categorie`"
				+ "	SET"
				+ "		`categorie`.`CategorieNaam` ='"+ c.getCatNName()+"'"
				+ "	WHERE"
				+ "		`categorie`.`CategorieNaam` ='"+ c.getCatName()+"'"; 
		try{
			stmt = openConnection().createStatement();
			stmt.executeUpdate(sqlUpdate);
					}
		catch(Exception e1){
			
		}}

	
	public void delete(Categorie c) {
		String sqlDelete = "DELETE FROM"
				+"		`categorie`"
				+"	WHERE"
				+"		`categorie`.`CategorieNaam` = '" + c.getCatName()+ "'";
		try {
			stmt = openConnection().createStatement();
			stmt.executeUpdate(sqlDelete);
					} 
		catch (Exception e2) {
		}}
	

	public Categorie find(Categorie c) {
		String sqlFind =  "Select * from categorie where CategorieNaam = " + c.getCatName();
		try {
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlFind);
			rs.next();
		} catch (SQLException sql2) {
			System.out.println(sql2.getErrorCode());
			System.out.println("Categorie naam niet gevonden");
		}
		return c;
	}

	
	public ArrayList<Categorie> loadall() {
		ArrayList<Categorie> catagorys = new ArrayList<Categorie>();
		String sqlLoadall = "Select * from categorie order by CategorieNaam";
		try {
			Categorie c;
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadall);
			while (rs.next()) {
				c = new Categorie(rs.getInt(1), rs.getString(2));
				catagorys.add(c);
				}} 
			catch (Exception e) {
			}
		return catagorys;
	}
}